<?php
class tx_chraetseltreff_loesungen extends tx_scheduler_Task
{
    public $confArray = array ( "uploadPath" => "uploads/tx_chraetseltreff",
                                "datenanlieferung" => "fileadmin/Datenanlieferung",
                                "sysfolder" => "1332"
                               );
    
    public function loesungen ($number)
    {
        $res = $GLOBALS [ 'TYPO3_DB' ]->exec_SELECTquery (
                'P.title PT, P.uid UID, evt_tag, evt_woche, filename, taktung, suffix, replyname, replyemail, P.email, R.*',
                'tx_chraetseltreff_print P LEFT JOIN tx_chraetseltreff_redaktion R ON P.email=R.uid',
                'P.pid='. $this->lConf [ "sysfolder" ].' AND P.deleted=0 AND P.hidden=0',
                '',
                'sorting ASC'
        );				
            
        while ( $a = $GLOBALS [ 'TYPO3_DB' ]->sql_fetch_assoc ( $res ) )
        {
            $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery(
                'S.title ST,  S.*, COUNT( * ) AS matches, P.*',
                'tx_chraetseltreff_user U 
                LEFT JOIN tx_chraetseltreff_spiel S ON U.uid=S.user_id
                LEFT JOIN tx_chraetseltreff_print P ON P.uid=S.print_id
                ',
                'S.print_id='.mysql_real_escape_string( $a["uid"]).
                ' AND S.kw='.$number.
                ' AND U.deleted=0 AND U.hidden=0 AND S.deleted=0 AND S.hidden=0',
                'ST',
                'matches DESC',
                '10'
            );
        
            $path = "../../../../uploads/tx_chraetseltreff/";
            $htmlfilename=$path."filecache.html";
            $pdf = array();
            $c=0;
            
            while ( $b = $GLOBALS [ 'TYPO3_DB' ]->sql_fetch_assoc ( $res ) )
            {
                if ($c==0)
                {
                    $title = $b["title"];
                    $replyemail = $b["replyemail"];
                    $replyname = $b["replyname"];
                    $toemail = $b["toemail"];
                    $toname = $b["toname"];
                    $emailbetreff = $b["emailsubject"];
                    $emailbody = $b["emailbody"];
                    $c++;
                }
                
                $liste = array();
                
                $innerloop = $GLOBALS['TYPO3_DB']->exec_SELECTquery( "*",
                            "tx_chraetseltreff_user U
                            INNER JOIN tx_chraetseltreff_spiel S ON U.uid=S.user_id",
                            "title=\"".$b['ST']."\"",
                            "",
                            "",
                            ""
                           );
                
                while ($d = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($innerloop))
                {
                    $liste[$d["email"]]=$d;	
                }
                
                $num = count($liste) >= 200 ? 200 : count($liste);
                $rand_keys = array_rand($liste, $num);
        
                $a = "<html><head></head><body><table>";
                foreach ($rand_keys as $k )
                {
                    $a .= "<tr><td>".$liste[$k]["title"]."</td><td>".($liste[$k]["anrede"]==0 ? "Frau" : "Herr")." ".
                            $liste[$k]["vorname"]." ".$liste[$k]["name"]."\r\n".
                            $liste[$k]["strasse"]." ".$liste[$k]["hausnummer"]."\r\n".
                            $liste[$k]["plz"]." ".$liste[$k]["ort"]."</td><td>".
                            $liste[$k]["hinweise"]."</td><td>".$liste[$k]["telefon"]."</td></tr>";	
                }
                $a.="</table><body></html>";
                
                $handle = fopen ($htmlfilename, "w+" );                    
                fwrite( $handle, $a );
                fclose ( $handle );
    
                $pdf[$b['ST']] = $path . "Lösungswort ".$b['ST'].".pdf";
                
                    // Run HTMLDOC to provide the PDF file to the user...
                $passthru = 'htmldoc -t pdf --quiet --jpeg --webpage --outfile '.'\''.$pdf[$b['ST']].'\' \''.$htmlfilename.'\'';
                passthru ( $passthru );	
            }
            
            $needle = array ("###MAGAZIN###", "###KW###");
            $replace = array ($title, $number);
            $emailbetreff=str_replace($needle,$replace,$emailbetreff);
            $body=str_replace($needle,$replace,$body);
            
            $mail = t3lib_div::makeInstance('t3lib_mail_message');
            $mail->setFrom(array($replyemail => $replyname));
            $mail->setTo(array($toemail => $toname));
            $mail->setSubject($betreff);
            $mail->setBody($body);
            $mail->addPart($body);
            
            foreach ($pdf as $k => $filename)
            {
                if (file_get_contents($filename))
                {
                    $mail->attach(Swift_Attachment::fromPath($filename));
                }	
            }
            $mail->send();
        }          
    }
   
    public function execute()
    {
        $q = "DELETE u1 From tx_chraetseltreff_user u1 JOIN tx_chraetseltreff_user u2 USING (email) WHERE u1.tstamp < u2.tstamp"; 
        $res = $GLOBALS['TYPO3_DB']->sql_query($q);
        $q = "DELETE s From tx_chraetseltreff_spiel s WHERE user_id NOT IN (SELECT uid from tx_chraetseltreff_user WHERE uid is not null)";
        $res = $GLOBALS['TYPO3_DB']->sql_query($q);
    
        if ( date('D', $time() ) == "Sat" )
        {
            $this->loesungen((int)date("W"));
            $this->loesungen((int)date("W")+1);
            $this->loesungen((int)date("n"));
            $this->loesungen((int)date("n")+1);
        }          
    }
}
?>